package cn.com.atlasdata.atops.module;

import cn.com.atlasdata.atops.helper.*;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import org.springframework.context.annotation.Configuration;


import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

/**
 * api 接口实现
 *
 * @author ZJH
 * @create 2018-08-29 17:40
 **/
@Configuration
//@EnableConfigurationProperties({Config.class})
public class Api {
    private static final Logger log = LoggerFactory.getLogger(Api.class);

    /***
     * 获取数据库类型及数量
     * @param type 数据库类型
     * @return 数据库类型及数量
     */
    public static  String getDatabaseInfo(String type,Config config){
        log.trace("getDatabaseInfo now:type=" + type);
        Connection conn = null;
        String sum = "0";
        try
        {
           conn = ConnectHelper.getConnection(config.getConfdb_host(),config.getConfdb_port(),config.getConfdb_dbname(),config.getConfdb_username(),config.getConfdb_password(), AtopsConstants.dbtype_mysql);
            if(null != conn){
                String sql = "select count(*) as sum from graph.endpoint";
                try(Statement stmt = conn.createStatement() ) {
                    ResultSet rs = stmt.executeQuery(sql);
                    while (rs.next()) {
                        sum = rs.getString("sum");
                        break;
                    }
                    rs.close();
                }
            }
        }catch (SQLException e){
            log.error("查询数据出错:" + e.toString(),e);
        }finally {
            try {
                if (null != conn && !conn.isClosed()) {
                    conn.close();
                    conn = null;
                }
            }catch (Exception e){
                log.error("关闭数据库连接出错:" + e.toString(),e);
            }
        }
        JSONObject retJson = new JSONObject();
        retJson.put("timestamp",System.currentTimeMillis()/1000);
        JSONArray values = new JSONArray();
        JSONObject value = new JSONObject();
        value.put("type","oracle");//暂时只有Oracle类型
        value.put("sum",Integer.valueOf(sum) - 1);//减去1是因为有一个localhost.domainname是open-falcon agent监控本地资源的，不是监控主机
        values.add(value);
        retJson.put("values",values);

        return  retJson.toJSONString();
    }

    /***
     * 保存延迟数据库信息
     * @param data 保存的数据
     * @return 保持成功返回succes，否则返回false
     */
    public static String saveDelayedDb(JSONObject data,Config config){
        //后续要把数据扔到Redis中，然后就返回成功标记，存储由定时任务执行
        //现在直接保存
        log.trace("saveDelayedDb new:" + data.toJSONString());
        Connection conn = null;
        String ret = "false";
        try
        {
            conn = ConnectHelper.getConnection(config.getDbcenter_host(),config.getDbcenter_port(),"falcon",config.getDbcenter_username(),config.getDbcenter_password(), AtopsConstants.dbtype_postgres);
            if(null != conn){
                String endpoint = data.getString("endpoint");
                String ip = data.getString("ip");
                String sid = data.getString("sid");
                String delay = data.getString("delay");
                String sql = "INSERT INTO delayed_db(timestamp,endpoint,ip,sid,delay)" +
                        " VALUES (CURRENT_TIMESTAMP," +
                        "'"+ endpoint +"','" +ip +"','" +sid +"','" +delay +"')";
                try(Statement stmt = conn.createStatement() ) {
                    stmt.execute(sql);
                }
                ret = "success";
            }
        }catch (Exception e){
            log.error("保存数据出错:" + e.toString(),e);
        }finally {
            try {
                if (null != conn && !conn.isClosed()) {
                    conn.close();
                    conn = null;
                }
            }catch (Exception e){
                log.error("关闭数据库连接出错:" + e.toString(),e);
            }
        }
        return ret;
    }

    /***
     * 获取延迟机器信息
     * @return 延迟机器列表信息（json字符串）
     */
    public static String getDelayedDb(Config config){
        log.trace("getDelayedDb now");
        Connection conn = null;
        JSONArray values = new JSONArray();
        try
        {
            conn = ConnectHelper.getConnection(config.getDbcenter_host(),config.getDbcenter_port(),"falcon",config.getDbcenter_username(),config.getDbcenter_password(), AtopsConstants.dbtype_postgres);
            if(null != conn){
                String sql = "select endpoint ,ip,sid,delay from delayed_db where timestamp >= current_timestamp - interval '2 min'";
                try(Statement stmt = conn.createStatement() ) {
                    ResultSet rs = null;
                    rs = stmt.executeQuery(sql);
                    while (rs.next()) {
                        JSONObject value = new JSONObject();
                        value.put("endpoint", rs.getString("endpoint"));
                        value.put("ip", rs.getString("ip"));
                        value.put("sid", rs.getString("sid"));
                        value.put("delay", rs.getString("delay"));
                        if (values.contains(value)) {
                            continue;
                        }
                        values.add(value);
                    }
                    rs.close();
                }
            }
        }catch (SQLException e){
            log.error("获取机器延迟信息出错:" + e.toString(),e);
        }finally {
            try {
                if (null != conn && !conn.isClosed()) {
                    conn.close();
                    conn = null;
                }
            }catch (Exception e){
                log.error("关闭数据库连接出错:" + e.toString(),e);
            }
        }
        JSONObject retJson = new JSONObject();
        retJson.put("timestamp",System.currentTimeMillis()/1000);
        retJson.put("values",values);

        return  retJson.toJSONString();
    }

    /***
     *get topN
     * @param type 类型
     * @param net_type net子类型，当type为net时有效
     * @return topN数据库信息（json字符串）
     */
    public static String getTopN(String type,String net_type,Config config){
        log.trace("getTopN now type=" + type + " net_type=" + net_type);

        //获取所有的endpoint
        String endpoint_url = "http://" + config.getConfdb_host() + ":8080/api/v1/grafana/metrics/find";
        String endpointQuery = HttpHelper.sendGet(endpoint_url,"query=.*");
        JSONArray endpointList = JSON.parseArray(endpointQuery);
        //查找最近2分钟内的数据
        long until = System.currentTimeMillis()/1000;
        long fromTime = until - 120;
        String url = "http://" + config.getConfdb_host() + ":8080/api/v1/grafana/render";
        List<JSONObject> retJsonList = new ArrayList<JSONObject>();
        String counterName = "host_CPU_utilization";//默认为cpu couter名称
        for (int i=0;i<endpointList.size();i++) {//遍历整个endpointlist，重新组装请求参数
            JSONObject endpoint = endpointList.getJSONObject(i);
            String endpointName = endpoint.getString("text");
            String param = "target=" + endpointName + "#";
            if ( "net_package".equals(net_type)) {//当net_type为net_package时，需要查询system_network_packets_received_per_sec/system_network_packets_send_per_sec两个counter的值

                counterName = "system_network_packets_received_per_sec";//接收包数
                String receivedParam = param + counterName + "&from=" + fromTime + "&until=" + until + "&format=json&maxDataPoints=1";
                counterName = "system_network_packets_send_per_sec";//发送包数
                String sendParam = param + counterName + "&from=" + fromTime + "&until=" + until + "&format=json&maxDataPoints=1";

                String receivedResult = HttpHelper.sendPost(url, receivedParam);
                JSONArray receivedResultArray = JSON.parseArray(receivedResult);

                String sendResult = HttpHelper.sendPost(url, receivedParam);
                JSONArray sendResultArray = JSON.parseArray(sendResult);
                long val = 0;
                if (!receivedResultArray.isEmpty()) {
                    JSONArray valuesArray = JSON.parseArray(receivedResultArray.getJSONObject(0).getString("Values"));
                    if (!valuesArray.isEmpty()) {
                        JSONObject valJson =  valuesArray.getJSONObject(0);
                        if(valJson.containsKey("value")) {
                            val = valuesArray.getJSONObject(0).getLong("value");
                        }
                        val = val + valuesArray.getJSONObject(0).getLong("value");
                    }
                }
                if (!sendResultArray.isEmpty()) {
                    JSONArray valuesArray = JSON.parseArray(sendResultArray.getJSONObject(0).getString("Values"));
                    if (!valuesArray.isEmpty()) {
                        JSONObject valJson =  valuesArray.getJSONObject(0);
                        if(valJson.containsKey("value")) {
                            val = valuesArray.getJSONObject(0).getLong("value");
                        }
                        val = val + valuesArray.getJSONObject(0).getLong("value");
                    }
                }
                JSONObject singleEndPoint = new JSONObject();
                singleEndPoint.put("endpoint", endpointName);
                singleEndPoint.put("value", String.valueOf(val));
                retJsonList.add(singleEndPoint);
            }else {
                if ("cpu".equals(type)) {
                    counterName = "host_CPU_utilization";
                } else if ("memory".equals(type)) {
                    counterName = "memory_total_usage";
                } else if ("disk".equals(type)) {
                    counterName = "user_transaction_per_sec";
                } else if ("net".equals(type)) {
                    counterName = "network_traffic_bytes_per_sec";//数据库每秒传输字节数
                }

                param = param + counterName + "&from=" + fromTime + "&until=" + until + "&format=json&maxDataPoints=1";
                String result = HttpHelper.sendPost(url, param);
                JSONArray resultArray = JSON.parseArray(result);
                String val = "0";
                if (!resultArray.isEmpty()) {
                    JSONArray valuesArray = JSON.parseArray(resultArray.getJSONObject(0).getString("Values"));
                    if (null != valuesArray && !valuesArray.isEmpty()) {
                        JSONObject valJson =  valuesArray.getJSONObject(0);
                        if(valJson.containsKey("value")) {
                            val = valuesArray.getJSONObject(0).getString("value");
                        }
                    }
                }
                JSONObject singleEndPoint = new JSONObject();
                singleEndPoint.put("endpoint", endpointName);
                singleEndPoint.put("value", val);
                retJsonList.add(singleEndPoint);
            }
        }
        //重写collections.sort方法,自定义排序
        Collections.sort( retJsonList, new Comparator<JSONObject>() {
            private static final String KEY_NAME = "value";//排序的KEY
            @Override
            public int compare(JSONObject a, JSONObject b) {
                String valA = new String();
                String valB = new String();
                try {
                    valA = (String) a.get(KEY_NAME);
                    valB = (String) b.get(KEY_NAME);
                }
                catch (JSONException e) {
                    log.error("排序出错：" +e.toString());
                }
                return -valA.compareTo(valB);
            }
        });
        List<JSONObject> retValues = new ArrayList<JSONObject>();
        for(int i=0;i<retJsonList.size();i++){
            retValues.add(retJsonList.get(i));
            if(i>=4){//只取前五个的值
                break;
            }
        }
        JSONObject retMap = new JSONObject();
        retMap.put("type",type);
        retMap.put("net_type",net_type);
        retMap.put("sum",retValues.size());
        retMap.put("timestamp",until);
        retMap.put("values",retValues);
        return  retMap.toJSONString();
    }

    /***
     * 获取topSQL
     * @param endpoint 端点名称
     * @param oderBy 排序字段
     * @param sum 数量
     * @return topsql字符串（json格式）
     */
    public static String getTopSql(String endpoint,String oderBy,int sum,Config config){

        log.trace("getTopSql now endpoint=" + endpoint + " oderBy=" + oderBy + " sum=" + sum );

        Connection conn = null;
        JSONObject retValues = new JSONObject();
        JSONArray values = new JSONArray();
        try
        {
            conn = ConnectHelper.getConnection(config.getDbcenter_host(),config.getDbcenter_port(),"top_sql",config.getDbcenter_username(),config.getDbcenter_password(), AtopsConstants.dbtype_postgres);
            if(null != conn){
                //这里取6分钟内的数据是因为topsql获取间隔为5分钟，为了确保结果不为空，故放大时间
                String sql = "select sql_text,sql_id,persistent_mem,sorts,executions,disk_reads,direct_writes,buffer_gets,user_io_wait_ms,cpu_time_ms,elapsed_time_ms,physical_read_bytes_kb,physical_write_bytes_kb from "
                        + endpoint
                        + " where  timestamp >= current_timestamp - interval '6 min'  order by "
                        +oderBy + " desc limit " +sum;
                try(Statement stmt = conn.createStatement() ) {
                    ResultSet rs = stmt.executeQuery(sql);
                    while (rs.next()) {
                        JSONObject value = new JSONObject(true);
                        value.put("sql_id", rs.getString("sql_id"));
                        value.put("sql_text", rs.getString("sql_text"));
                        value.put("persistent_mem", rs.getFloat("persistent_mem"));
                        value.put("sorts", rs.getLong("sorts"));
                        value.put("executions", rs.getLong("executions"));
                        value.put("disk_reads", rs.getLong("disk_reads"));
                        value.put("direct_writes", rs.getLong("direct_writes"));
                        value.put("buffer_gets", rs.getLong("buffer_gets"));
                        value.put("user_io_wait_ms", rs.getInt("user_io_wait_ms"));
                        value.put("cpu_time_ms", rs.getInt("cpu_time_ms"));
                        value.put("elapsed_time_ms", rs.getInt("elapsed_time_ms"));
                        value.put("physical_read_bytes_kb", rs.getLong("physical_read_bytes_kb"));
                        value.put("physical_write_bytes_kb", rs.getLong("physical_write_bytes_kb"));

                        values.add(value);
                    }
                    rs.close();
                }
            }
        }catch (Exception e){
            log.error("获取topSQL出错:" + e.toString(),e);
        }finally {
            try {
                if (null != conn && !conn.isClosed()) {
                    conn.close();
                    conn = null;
                }
            }catch (Exception e){
                log.error("关闭连接出错:" + e.toString(),e);
            }
        }
        retValues.put("endpoint",endpoint);
        retValues.put("timestamp",System.currentTimeMillis()/1000);
        retValues.put("sum",values.size());
        retValues.put("values",values);
        return retValues.toJSONString();
    }

    /**
     * 保存topSQL
     * @param data 保存数据
     * @return 保持成功返回succes，否则返回false
     */
    public  static String saveTopSql(JSONObject data,Config config){
        //后续要把数据扔到Redis中，然后就返回成功标记，存储由定时任务执行
        //现在直接保存
        log.trace("saveTopSql:" + data.toJSONString());
        Connection conn = null;
        String ret = "false";
        try
        {
            conn = ConnectHelper.getConnection(config.getDbcenter_host(),config.getDbcenter_port(),"top_sql",config.getDbcenter_username(),config.getDbcenter_password(), AtopsConstants.dbtype_postgres);
            if(null != conn) {
                conn.setAutoCommit(false);//关闭自动commit
                String endpoint = data.getString("endpoint");
//                String sid = data.getString("sid");
                int step = data.getInteger("step");
                String tags = data.getString("tags");
                String counterType = data.getString("counterType");
                JSONArray values = data.getJSONArray("values");
                if (values.isEmpty()) {//当values中没有值时，直接返回错误
                    return ret;
                }
                //创建表语句
                String createSql = "create table if not exists " + endpoint + "(\n" +
                        "timestamp timestamp,\n" +
                        "sql_text varchar,\n" +
                        "sql_id varchar,\n" +
                        "persistent_mem float,\n" +
                        "sorts bigint,\n" +
                        "executions bigint,\n" +
                        "disk_reads bigint,\n" +
                        "direct_writes bigint,\n" +
                        "buffer_gets bigint,\n" +
                        "user_io_wait_ms int,\n" +
                        "cpu_time_ms int,\n" +
                        "elapsed_time_ms int,\n" +
                        "physical_read_bytes_kb bigint,\n" +
                        "physical_write_bytes_kb bigint,\n" +
                        "step int,\n" +
                        "counterType varchar,\n" +
                        "tags varchar)";
                //判断top_sql.endpoint表是否存在，不存在则创建
                try (Statement stmt = conn.createStatement()) {
                    stmt.execute(createSql);
                    conn.commit();
                }
                String insertSql = "INSERT INTO " + endpoint
                        + "(timestamp,sql_text,sql_id,persistent_mem,sorts,executions,disk_reads,direct_writes,buffer_gets,user_io_wait_ms,cpu_time_ms,elapsed_time_ms,physical_read_bytes_kb,physical_write_bytes_kb,step,counterType,tags) "
                        + "VALUES(CURRENT_TIMESTAMP,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";//插入语句

                try (Statement pstmt = conn.prepareStatement(insertSql)) {
                    //使用批量插入,组装值，并插入到表top_sql.endpoint中
                    for (int i = 0; i < values.size(); i++) {
                        JSONObject value = values.getJSONObject(i);
                        String sqlText = value.getString("sql_text");
                        sqlText = sqlText.replace("'", "''");
                        String sqlId = value.getString("sql_id");
                        float persistentMem = value.getFloat("persistent_mem");
                        long sorts = value.getLong("sorts");
                        long executions = value.getLong("executions");
                        long diskReads = value.getLong("disk_reads");
                        long directWrites = value.getLong("direct_writes");
                        long bufferGets = value.getLong("buffer_gets");
                        int userIoWaitMs = value.getInteger("user_io_wait_ms");
                        int cpuTimeMs = value.getInteger("cpu_time_ms");
                        int elapsedTimeMs = value.getInteger("elapsed_time_ms");
                        long physicalReadBytesKb = value.getLong("physical_read_bytes_kb");
                        long physicalWriteBytesKb = value.getLong("physical_write_bytes_kb");
                        ((PreparedStatement) pstmt).setString(1,"'"+sqlText + "'");
                        ((PreparedStatement) pstmt).setString(2,"'"+sqlId + "'");
                        ((PreparedStatement) pstmt).setFloat(3,persistentMem);
                        ((PreparedStatement) pstmt).setLong(4,sorts);
                        ((PreparedStatement) pstmt).setLong(5,executions);
                        ((PreparedStatement) pstmt).setLong(6,diskReads);
                        ((PreparedStatement) pstmt).setLong(7,directWrites);
                        ((PreparedStatement) pstmt).setLong(8,bufferGets);
                        ((PreparedStatement) pstmt).setInt(9,userIoWaitMs);
                        ((PreparedStatement) pstmt).setInt(10,cpuTimeMs);
                        ((PreparedStatement) pstmt).setInt(11,elapsedTimeMs);
                        ((PreparedStatement) pstmt).setLong(12,physicalReadBytesKb);
                        ((PreparedStatement) pstmt).setLong(13,physicalWriteBytesKb);
                        ((PreparedStatement) pstmt).setInt(14,step);
                        ((PreparedStatement) pstmt).setString(15,counterType);
                        ((PreparedStatement) pstmt).setString(16,tags);
                        ((PreparedStatement) pstmt).addBatch();
                    }
                    pstmt.executeBatch();
                    conn.commit();
            }
            ret = "success";
            }
        }catch (Exception e){
            log.error("保存topSQL出错:" + e.toString(),e);
        }finally {
            try {
                if (null != conn && !conn.isClosed()) {
                    conn.close();
                    conn = null;
                }
            }catch (Exception e){
                log.error("关闭连接出错:" + e.toString(),e);
            }
        }
        return ret;
    }
}
